RDS Proxyが無意味になる恐怖の現象「ピン留め」を回避するための基本的な設定値について

ピン留め怖い

#RDS Proxy

#Amazon RDS

#AWS

岩田智哉

2020.07.26

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

CX事業本部＠大阪の岩田です。

RDS Proxyを利用するとRDS ProxyにプールされたDB接続を複数のDBクライアントで使い回すことができ、限られたDB接続を効率的に利用することが可能になります。しかし複数のDBクライアントが安全にDB接続を共有できない場合、RDSProxyはコネクションプール内のDB接続を特定のDBクライアントに対して固定してしまいます。これが「ピン留め」と呼ばれる現象で、このピン留めが発生するとRDS Proxyを利用するメリットが失われてしまいます。

このブログでは「ピン留め」を回避するための基本的なパラメータ調整についてご紹介します。

環境

今回利用した環境です

こちらのブログとほぼ同様の設定にしてクライアントからの同時接続数が実質1に制限されるようにしています。

RDS for PostgreSQL 11.8-R1
- インスタンスクラス db.t3.micro
- max_connections: 9
RDS Proxy
- エンジンの互換性: PostgreSQL
- 接続プールの最大接続数: 100
- 接続借用タイムアウト: 10秒

元々はPostgreSQLだけで検証するつもりでしたが、セッション固定フィルタの部分が意図通りに動かなかったので、そこだけMySQLで検証しています。利用したのは以下の設定です。

RDS for MySQL 5.7.30
- インスタンスクラス db.t3.micro
- max_connections: 4

ピン留めの発生条件について

まずピン留めが発生する条件についておさらいしておきましょう。少し長いですが公式ドキュメントからの引用です。

The proxy pins the session to the current connection in the following situations where multiplexing might cause unexpected behavior:

Any statement with a text size greater than 16 KB causes the proxy to pin the session.

Prepared statements cause the proxy to pin the session. This rule applies whether the prepared statement uses SQL text or the binary protocol.

Explicit MySQL statements LOCK TABLE, LOCK TABLES, or FLUSH TABLES WITH READ LOCK cause the proxy to pin the session.

Setting a user variable or a system variable (with some exceptions) causes the proxy to pin the session. If this situation reduces your connection reuse too much, you can choose for SET operations not to cause pinning. For information about how to do so by setting the SessionPinningFilters property, see Creating an RDS Proxy.

Creating a temporary table causes the proxy to pin the session. That way, the contents of the temporary table are preserved throughout the session regardless of transaction boundaries.

Calling the MySQL functions ROW_COUNT, FOUND_ROWS, and LAST_INSERT_ID sometimes causes pinning.

The exact circumstances where these functions cause pinning might differ between Aurora MySQL versions that are compatible with MySQL 5.6 and MySQL 5.7.

For PostgreSQL, the following interactions cause pinning:

Using SET commands

Using the extended query protocol such as by using JDBC default settings

Creating temporary sequences, tables, or views

Declaring cursors

Discarding the session state

Listening on a notification channel

Loading a library module such as auto_explain

Manipulating sequences using functions such as nextval() and setval()

Interacting with locks using functions such as pg_advisory_lock() and pg_try_advisory_lock()

Using prepared statements, setting parameters, or resetting a parameter to its default

Avoiding Pinning

変数の設定、SETコマンドの利用、Prepared Statementの利用などによってピン留めが引き起こされます。また、MySQLよりもPostgreSQLの方がピン留めを発生させるオペレーションが多いことが分かります。特にJavaユーザーにとっては拡張クエリプロトコルがピン留め対象となる点は要注意です。何も意識せずにデフォルトの設定のままでJDBCからRDS Proxyに接続すると、気づかぬうちにピン留め対象になっていたという悲しい状況になりそうです。

2023/11/1０付けのアップデートにて拡張クエリプロトコルはピン留めの対象外となりました

SETコマンドによるピン留め

ピン留めの発生条件としてSETコマンドの利用が挙げられましたが、以下パラメータのSETに関してはRDS Proxy側でパラメータの変更を追跡し、設定値が同一のクライアント間でコネクションプール内のDB接続を共有できるように調整してくれます。

character set
collation
time zone
autocommit
current database
SQL mode
session_track_schema

図にするとこんなイメージでしょうか

charset=UTF-8のクライアントが2つ、charset=EUC-JPのクライアントが2つがRDS Proxyに同時に接続している状況ではcharset=UTF-8のクライアント間でプール内の1つのDB接続を共有利用し、charset=EUC=JPのクライアントはプール内の別のDB接続を共有利用します。実際に挙動を確認してみましょう。

まず1つ目のシェルからpsqlでRDS Proxyに接続し、SQLを発行します。

$ psql -h <RDSプロキシのエンドポイント> -U proxy_user template1
Password for user proxy_user:
psql (11.5, server 11.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

template1=> select now();
              now
-------------------------------
 2020-07-26 08:12:06.179139+00
(1 row)

次に2つ目のシェルからpsqlでRDS Proxyに接続し、SQLを発行します。

psql -h <RDSプロキシのエンドポイント> -U proxy_user template1
Password for user proxy_user:
psql (11.5, server 11.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

template1=> select now();
              now
-------------------------------
 2020-07-26 08:12:50.319269+00
(1 row)

最大同時接続数を実質1に制限した状態ですが、RDS Proxyを介しているため2つのpsqlセッションからSQLが発行できています。

今度はSET文でcharacter setを設定してみます。まず1つ目のpsqlセッションです

template1=> set client_encoding ='UTF-8';
SET
template1=> select now();
              now
-------------------------------
 2020-07-26 08:17:59.843794+00
(1 row)

続いて2つ目のpsqlセッションです

template1=> select now();
ERROR:  Timed-out waiting to acquire database connection

今度はSQLの実行に失敗しました。1つ目のpsqlセッションがSET文を実行したため、セッションが共有できなくなったためです。

2つ目のpsqlセッションからもSET文でcharacter setを設定し、再度SQLを発行してみましょう。

template1=> set client_encoding ='UTF-8';
SET
template1=> select now();
              now
-------------------------------
 2020-07-26 08:18:34.966984+00
(1 row)

今度はSQLの発行に成功しました。character setが同一のpsqlセッション間ではコネクションプール内のDB接続が共有利用できていることが分かります。

ピン留めを回避するためのRDS Proxyのパラメータ

ピン留めの発生条件について解説してきましたが、ピン留めを回避するためにはどのような対策が取れるのでしょうか？

ピン留めを回避するためのRDS Proxy側の設定項目として

セッション固定フィルタ
初期化クエリ

という設定値が存在します。

セッション固定フィルタ

この設定を利用すると、特定の操作をピン留め対象外にすることができます。特定の操作をピン留めの対象外にしてもアプリケーションに不具合が発生しないことを把握できている場合は、この設定の利用を検討すると良いでしょう。ただし、現時点(2020/7/26)で利用可能な設定値はEXCLUDE_VARIABLE_SETSのみとなっており、変数の設定以外にピン留め対象外とする操作は指定できません。今後のアップデートよって、より多くの操作が指定できるようになることを期待したいですね。

初期化クエリ

このパラメータにはセミコロン区切りでSQL文を指定することができ、指定されたSQLはRDS ProxyからRDSに接続を確立する際の初期化処理として実行されます。アプリケーション要件としてSET文によるセッションの初期化が必要な場合は、必要なSET文アプリケーション側ではなく、RDS Proxyの初期化クエリから実行することでピン留めを回避することが可能です。

やってみる

セッション固定フィルタ

まず初期化クエリを何も指定していない状態で変数設定時の挙動を確認します。

1つ目のpsqlセッションから適当な変数myvar.hogeをセットしてみます

$ psql -h <RDSプロキシのエンドポイント> -U proxy_user template1
Password for user proxy_user:
psql (11.5, server 11.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

template1=> set myvar.hoge = 1;
SET
template1=> show myvar.hoge;
 myvar.hoge
------------
 1
(1 row)

2つ目のpsqlセッションからSQLの発行を試みます。

$ psql -h <RDSプロキシのエンドポイント> -U proxy_user template1
Password for user proxy_user:
psql (11.5, server 11.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

template1=> select now();
ERROR:  Timed-out waiting to acquire database connection

変数を設定したことで、1つ目のpsqlセッションにピン留めが発生しているため、2つ目のpsqlセッションはエラーとなりました。今度はセッション固定フィルタを設定してから同様の処理を試してみましょう。

まずは1つ目のpsqlセッション

$ psql -h <RDSプロキシのエンドポイント> -U proxy_user template1
Password for user proxy_user:
psql (11.5, server 11.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

template1=> set myvar.hoge = 1;
SET
template1=> show myvar.hoge;
 myvar.hoge
------------
 1
(1 row)

続いて2つ目のpsqlセッション

$ psql -h <RDSプロキシのエンドポイント> -U proxy_user template1
Password for user proxy_user:
psql (11.5, server 11.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

template1=> select now();
ERROR:  Timed-out waiting to acquire database connection

あれ？！エラーになってしまいました...

ドキュメントを見返してもよく分からなかったのですが、セッション固定フィルタのEXCLUDE_VARIABLE_SETSが有効に機能するのはMySQLの場合だけなのかなー？？と当たりをつけて、MySQLで再挑戦を試みました。

セッション固定フィルタ(MySQLで再挑戦)

PostgreSQLの振る舞いが期待通りにならなかったので、MySQLの環境を構築して再挑戦してみました。

まずRDS Proxyに対して2つのmysqlセッションを確立します。

1つ目

$ mysql -h <RDS Proxyのエンドポイント> -u proxy_user  -p proxy_db
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3557069843
Server version: 5.7.30 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2つ目

$ mysql -h <RDS Proxyのエンドポイント> -u proxy_user  -p proxy_db
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3557069843
Server version: 5.7.30 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

1つ目のmysqlセッションから適当なセッション変数myvarをセットします

mysql> SET @myvar = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> select @myvar;
+--------+
| @myvar |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

2つ目のmysqlセッションからSQLの発行を試みます。

mysql> select now();
ERROR 9501 (HY000): Timed-out waiting to acquire database connection

変数を設定したことで、1つ目のmysqlセッションにピン留めが発生しているため、2つ目のmysqlセッションからのSQL発行はエラーとなりました。今度はセッション固定フィルタを設定してから同様の処理を試してみましょう。

先ほどと同様にMySQLのセッションを2つ確立した後、1つ目のセッションから変数を設定します

mysql> SET @myvar = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> select @myvar;
+--------+
| @myvar |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

続いて2つ目のセッションからSQLを発行します

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-07-26 12:05:25 |
+---------------------+
1 row in set (0.01 sec)

mysql>select @myvar;
+--------+
| @myvar |
+--------+
|      1 |
+--------+
1 row in set (0.02 sec)

今度はSQLの発行に成功しました！1つ目のセッションで設定した変数myvarの中身が2つ目のセッションからも見えています。このセッション間で変数が共有されるという動作が許容できるのであればセッション固定フィルタにEXCLUDE_VARIABLE_SETSを設定するのもアリかもしれません。

初期化クエリ

続いて初期化クエリの動作を確認します。まず初期化クエリを何も指定していない状態でSET文実行時の挙動を確認します。※ここからは再度PostgreSQLで検証しています。

1つ目のpsqlセッションからwork_memをセットしてみます

$ psql -h <RDSプロキシのエンドポイント> -U proxy_user template1
Password for user proxy_user:
psql (11.5, server 11.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

template1=> set work_mem ='1MB';
SET

2つ目のpsqlセッションからRDS Proxyに接続し、SQLの発行を試みます。

$ psql -h <RDSプロキシのエンドポイント> -U proxy_user template1
Password for user proxy_user:
psql (11.5, server 11.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

template1=> select now();
FATAL:  Request returned an error: remaining connection slots are reserved for non-replication superuser and rds_superuser connections
SSL connection has been closed unexpectedly
The connection to the server was lost. Attempting reset: Succeeded.

1つ目のpsqlセッションにピン留めが発生しているため、2つ目のpsqlセッションはエラーとなりました。今度は1つ目のpsqlセッションから実行していたset work_mem ='1MB';をRDS Proxyの初期化クエリに移動してから試してみましょう。

改めて1つ目のpsqlセッションからRDS Proxyに接続し、SQLを発行します。今度はshow work_memも実行しています。

$ psql -h <RDSプロキシのエンドポイント> -U proxy_user template1
Password for user proxy_user:
psql (11.5, server 11.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

template1=> select now();
              now
-------------------------------
 2020-07-26 09:33:13.510346+00
(1 row)

template1=> show work_mem ;
 work_mem
----------
 1MB
(1 row)

2つ目のpsqlセッションからも同様のSQLを発行します

$ psql -h <RDSプロキシのエンドポイント> -U proxy_user template1
Password for user proxy_user:
psql (11.5, server 11.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

template1=> select now();
              now
-------------------------------
 2020-07-26 09:33:22.442599+00
(1 row)

template1=> show work_mem ;
 work_mem
----------
 1MB
(1 row)

特にエラーなく実行することができました。SET文を初期化クエリに移動したことで、2つのpsqlセッションがwork_mem= 1MBのDB接続を共有利用できていることが分かります。

ピン留めの発生状況を把握するには？

今回は検証用の構成として最大同時接続数が実質1になるような設定のRDSを利用し、かつ手動でSQLを実行しながら挙動を確認しました。しかし、実業務で「ピン留め」が発生した場合は、接続エラーのような分かりやすいエラーは出ないことが多いでしょう。Cloud WatchのメトリクスDatabaseConnectionsCurrentlySessionPinnedを確認することで、ピン留めの発生有無や発生頻度が確認できるので、RDS Proxyを利用する際は上記メトリクスを監視するように注意しましょう。

まとめ

RDS Proxyの「ピン留め」を回避するための基本的な設定についてご紹介しました。セッション固定フィルタの選択肢が現状はEXCLUDE_VARIABLE_SETSしか提供されていないため、設定レベルで対応できることは少ないです。どちらかというとアプリケーション側を改修して「ピン留め」を回避するようなケースが多くなりそうです。

何かしらのアプリケーションフレームワークやライブラリを利用してRDS Proxyに接続する場合、フレームワーク/ライブラリが自動的に発行するSET文によってピン留めが発生することも考えられるので要注意です。自動的にSET文を発行するようなフレームワーク/ライブラリを利用する場合はSET文が自動実行されないように調整しつつ、SET文を初期化クエリの方に移動させる必要があるかもしれません。ただし、自動発行されるSET文が変更しているパラメータが文字コードや照合順序だけであれば、同一アプリケーション間ではDB接続を共有できるので、初期化クエリへの移動は不要です。RDS Proxyを利用される際は、フレームワークの初期化処理の内容について再確認しておくと良いでしょう。

また、フレームワーク/ライブラリ側の設定でSET文の自動実行を抑止でいない可能性も考慮しておきましょう。例えばですがJavascript(TypeScript)の開発で人気のPrismaは「ピン留め」を回避する手段が無いようです。

Connection Pinning with AWS RDS Proxy

SET文以外にもPrepared Statementがピン留め対象となることは要注意です。各種のORMはクエリ実行時に裏側で自動的にPrepared Statementを利用するような振る舞いをすることが多いと思います。Prepared Statementを利用するとRDS Proxyの「ピン留め」対象となってしまうので、RDS Proxyを利用したい場合はフレームワーク/ライブラリとRDS Proxyの相性までしっかりと考えて技術選定を行いましょう。